{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 1,
   "id": "dc9692dc-53fb-47a7-9970-b2246633cf60",
   "metadata": {},
   "outputs": [],
   "source": [
    "import numpy as np\n",
    "import pandas as pd"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "d741c2e5-6679-444e-b513-5074bc0ea041",
   "metadata": {},
   "source": [
    "## 读取数据"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "id": "a76fa390-9e0f-492f-a273-c7949ddbdc84",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>ts_code</th>\n",
       "      <th>trade_date</th>\n",
       "      <th>open</th>\n",
       "      <th>high</th>\n",
       "      <th>low</th>\n",
       "      <th>close</th>\n",
       "      <th>pre_close</th>\n",
       "      <th>change</th>\n",
       "      <th>pct_chg</th>\n",
       "      <th>vol</th>\n",
       "      <th>amount</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>600519.SH</td>\n",
       "      <td>20241211</td>\n",
       "      <td>1540.00</td>\n",
       "      <td>1555.00</td>\n",
       "      <td>1530.98</td>\n",
       "      <td>1535.60</td>\n",
       "      <td>1546.59</td>\n",
       "      <td>-10.99</td>\n",
       "      <td>-0.7106</td>\n",
       "      <td>29671.12</td>\n",
       "      <td>4569662.604</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>600519.SH</td>\n",
       "      <td>20241210</td>\n",
       "      <td>1570.00</td>\n",
       "      <td>1579.73</td>\n",
       "      <td>1545.18</td>\n",
       "      <td>1546.59</td>\n",
       "      <td>1518.80</td>\n",
       "      <td>27.79</td>\n",
       "      <td>1.8297</td>\n",
       "      <td>60312.10</td>\n",
       "      <td>9421340.719</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>600519.SH</td>\n",
       "      <td>20241209</td>\n",
       "      <td>1522.02</td>\n",
       "      <td>1529.72</td>\n",
       "      <td>1513.20</td>\n",
       "      <td>1518.80</td>\n",
       "      <td>1521.01</td>\n",
       "      <td>-2.21</td>\n",
       "      <td>-0.1453</td>\n",
       "      <td>19799.86</td>\n",
       "      <td>3008173.586</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>600519.SH</td>\n",
       "      <td>20241206</td>\n",
       "      <td>1513.00</td>\n",
       "      <td>1538.90</td>\n",
       "      <td>1508.07</td>\n",
       "      <td>1521.01</td>\n",
       "      <td>1511.00</td>\n",
       "      <td>10.01</td>\n",
       "      <td>0.6625</td>\n",
       "      <td>32039.69</td>\n",
       "      <td>4883148.600</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>600519.SH</td>\n",
       "      <td>20241205</td>\n",
       "      <td>1510.86</td>\n",
       "      <td>1517.86</td>\n",
       "      <td>1510.00</td>\n",
       "      <td>1511.00</td>\n",
       "      <td>1520.00</td>\n",
       "      <td>-9.00</td>\n",
       "      <td>-0.5921</td>\n",
       "      <td>16141.47</td>\n",
       "      <td>2441318.387</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "     ts_code  trade_date     open     high      low    close  pre_close  \\\n",
       "0  600519.SH    20241211  1540.00  1555.00  1530.98  1535.60    1546.59   \n",
       "1  600519.SH    20241210  1570.00  1579.73  1545.18  1546.59    1518.80   \n",
       "2  600519.SH    20241209  1522.02  1529.72  1513.20  1518.80    1521.01   \n",
       "3  600519.SH    20241206  1513.00  1538.90  1508.07  1521.01    1511.00   \n",
       "4  600519.SH    20241205  1510.86  1517.86  1510.00  1511.00    1520.00   \n",
       "\n",
       "   change  pct_chg       vol       amount  \n",
       "0  -10.99  -0.7106  29671.12  4569662.604  \n",
       "1   27.79   1.8297  60312.10  9421340.719  \n",
       "2   -2.21  -0.1453  19799.86  3008173.586  \n",
       "3   10.01   0.6625  32039.69  4883148.600  \n",
       "4   -9.00  -0.5921  16141.47  2441318.387  "
      ]
     },
     "execution_count": 2,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# 成分股数据\n",
    "df=pd.read_excel(\"df_all.xlsx\",index_col=0)\n",
    "df.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "id": "b84a2127-7a56-4452-a5bd-e58afa8f88be",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>成份券代码Constituent Code</th>\n",
       "      <th>成份券名称Constituent Name</th>\n",
       "      <th>交易所Exchange</th>\n",
       "      <th>权重(%)weight</th>\n",
       "      <th>地区</th>\n",
       "      <th>ts_code</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>600519</td>\n",
       "      <td>贵州茅台</td>\n",
       "      <td>上海证券交易所</td>\n",
       "      <td>4.767</td>\n",
       "      <td>SH</td>\n",
       "      <td>600519.SH</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>300750</td>\n",
       "      <td>宁德时代</td>\n",
       "      <td>深圳证券交易所</td>\n",
       "      <td>3.430</td>\n",
       "      <td>SZ</td>\n",
       "      <td>300750.SZ</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>601318</td>\n",
       "      <td>中国平安</td>\n",
       "      <td>上海证券交易所</td>\n",
       "      <td>2.851</td>\n",
       "      <td>SH</td>\n",
       "      <td>601318.SH</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>600036</td>\n",
       "      <td>招商银行</td>\n",
       "      <td>上海证券交易所</td>\n",
       "      <td>2.238</td>\n",
       "      <td>SH</td>\n",
       "      <td>600036.SH</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>300059</td>\n",
       "      <td>东方财富</td>\n",
       "      <td>深圳证券交易所</td>\n",
       "      <td>1.713</td>\n",
       "      <td>SZ</td>\n",
       "      <td>300059.SZ</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   成份券代码Constituent Code 成份券名称Constituent Name 交易所Exchange  权重(%)weight  地区  \\\n",
       "0                 600519                  贵州茅台     上海证券交易所        4.767  SH   \n",
       "1                 300750                  宁德时代     深圳证券交易所        3.430  SZ   \n",
       "2                 601318                  中国平安     上海证券交易所        2.851  SH   \n",
       "3                 600036                  招商银行     上海证券交易所        2.238  SH   \n",
       "4                 300059                  东方财富     深圳证券交易所        1.713  SZ   \n",
       "\n",
       "     ts_code  \n",
       "0  600519.SH  \n",
       "1  300750.SZ  \n",
       "2  601318.SH  \n",
       "3  600036.SH  \n",
       "4  300059.SZ  "
      ]
     },
     "execution_count": 3,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# 沪深300指数数据\n",
    "df300=pd.read_excel(\"df_300_w.xlsx\",index_col=0)\n",
    "df300.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "id": "acbb814f-de34-4193-af61-4753ae9b5dba",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>成份券代码Constituent Code</th>\n",
       "      <th>成份券名称Constituent Name</th>\n",
       "      <th>交易所Exchange</th>\n",
       "      <th>权重(%)weight</th>\n",
       "      <th>地区</th>\n",
       "      <th>ts_code</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>600519</td>\n",
       "      <td>贵州茅台</td>\n",
       "      <td>上海证券交易所</td>\n",
       "      <td>4.767</td>\n",
       "      <td>SH</td>\n",
       "      <td>600519.SH</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>300750</td>\n",
       "      <td>宁德时代</td>\n",
       "      <td>深圳证券交易所</td>\n",
       "      <td>3.430</td>\n",
       "      <td>SZ</td>\n",
       "      <td>300750.SZ</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>601318</td>\n",
       "      <td>中国平安</td>\n",
       "      <td>上海证券交易所</td>\n",
       "      <td>2.851</td>\n",
       "      <td>SH</td>\n",
       "      <td>601318.SH</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>600036</td>\n",
       "      <td>招商银行</td>\n",
       "      <td>上海证券交易所</td>\n",
       "      <td>2.238</td>\n",
       "      <td>SH</td>\n",
       "      <td>600036.SH</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>300059</td>\n",
       "      <td>东方财富</td>\n",
       "      <td>深圳证券交易所</td>\n",
       "      <td>1.713</td>\n",
       "      <td>SZ</td>\n",
       "      <td>300059.SZ</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   成份券代码Constituent Code 成份券名称Constituent Name 交易所Exchange  权重(%)weight  地区  \\\n",
       "0                 600519                  贵州茅台     上海证券交易所        4.767  SH   \n",
       "1                 300750                  宁德时代     深圳证券交易所        3.430  SZ   \n",
       "2                 601318                  中国平安     上海证券交易所        2.851  SH   \n",
       "3                 600036                  招商银行     上海证券交易所        2.238  SH   \n",
       "4                 300059                  东方财富     深圳证券交易所        1.713  SZ   \n",
       "\n",
       "     ts_code  \n",
       "0  600519.SH  \n",
       "1  300750.SZ  \n",
       "2  601318.SH  \n",
       "3  600036.SH  \n",
       "4  300059.SZ  "
      ]
     },
     "execution_count": 4,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# 根据权重从大到小选择前20支股票\n",
    "df20=df300.loc[:19,:]\n",
    "df20.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "a645200d-fb9a-4dd6-8f7d-aaf263e6813e",
   "metadata": {},
   "source": [
    "## 变成时间序列数据\n",
    "每一列为一个股票在3年的涨跌幅数据"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "id": "aa05c4a9-4aec-48b7-a7d9-e8c98399831d",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th>pct_chg</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>trade_date</th>\n",
       "      <th>ts_code</th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th rowspan=\"5\" valign=\"top\">20211210</th>\n",
       "      <th>300014.SZ</th>\n",
       "      <td>1.2918</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>300015.SZ</th>\n",
       "      <td>-2.9965</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>300033.SZ</th>\n",
       "      <td>-3.1319</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>300059.SZ</th>\n",
       "      <td>-0.8864</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>300122.SZ</th>\n",
       "      <td>-2.1301</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                      pct_chg\n",
       "trade_date ts_code           \n",
       "20211210   300014.SZ   1.2918\n",
       "           300015.SZ  -2.9965\n",
       "           300033.SZ  -3.1319\n",
       "           300059.SZ  -0.8864\n",
       "           300122.SZ  -2.1301"
      ]
     },
     "execution_count": 5,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df1=pd.pivot_table(df,index=[\"trade_date\",\"ts_code\"],values=[\"pct_chg\"])\n",
    "df1.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "3264f654-f2d1-4d86-93a5-11bce2e993e5",
   "metadata": {},
   "source": [
    "找一天算一下涨跌幅"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "id": "11faa48a-7705-44e0-bb2e-3473c54e47e2",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>trade_date</th>\n",
       "      <th>ts_code</th>\n",
       "      <th>pct_chg</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>20211210</td>\n",
       "      <td>300014.SZ</td>\n",
       "      <td>1.2918</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>20211210</td>\n",
       "      <td>300015.SZ</td>\n",
       "      <td>-2.9965</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>20211210</td>\n",
       "      <td>300033.SZ</td>\n",
       "      <td>-3.1319</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>20211210</td>\n",
       "      <td>300059.SZ</td>\n",
       "      <td>-0.8864</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>20211210</td>\n",
       "      <td>300122.SZ</td>\n",
       "      <td>-2.1301</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   trade_date    ts_code  pct_chg\n",
       "0    20211210  300014.SZ   1.2918\n",
       "1    20211210  300015.SZ  -2.9965\n",
       "2    20211210  300033.SZ  -3.1319\n",
       "3    20211210  300059.SZ  -0.8864\n",
       "4    20211210  300122.SZ  -2.1301"
      ]
     },
     "execution_count": 6,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# 300支股票20241211涨跌幅\n",
    "df_i=df1.query('trade_date == [20211210]')\n",
    "df_i=df_i.reset_index()\n",
    "df_i.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "id": "113516c1-af8a-490f-931d-038cda9a674c",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>trade_date</th>\n",
       "      <th>ts_code</th>\n",
       "      <th>pct_chg</th>\n",
       "      <th>权重(%)weight</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>20211210</td>\n",
       "      <td>300014.SZ</td>\n",
       "      <td>1.2918</td>\n",
       "      <td>0.297</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>20211210</td>\n",
       "      <td>300015.SZ</td>\n",
       "      <td>-2.9965</td>\n",
       "      <td>0.341</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>20211210</td>\n",
       "      <td>300033.SZ</td>\n",
       "      <td>-3.1319</td>\n",
       "      <td>0.359</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>20211210</td>\n",
       "      <td>300059.SZ</td>\n",
       "      <td>-0.8864</td>\n",
       "      <td>1.713</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>20211210</td>\n",
       "      <td>300122.SZ</td>\n",
       "      <td>-2.1301</td>\n",
       "      <td>0.175</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   trade_date    ts_code  pct_chg  权重(%)weight\n",
       "0    20211210  300014.SZ   1.2918        0.297\n",
       "1    20211210  300015.SZ  -2.9965        0.341\n",
       "2    20211210  300033.SZ  -3.1319        0.359\n",
       "3    20211210  300059.SZ  -0.8864        1.713\n",
       "4    20211210  300122.SZ  -2.1301        0.175"
      ]
     },
     "execution_count": 7,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# 连表\n",
    "df_i_w=pd.merge(df_i,df300[['权重(%)weight','ts_code']],how='inner',on='ts_code')\n",
    "df_i_w.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "id": "2c7d8273-fbac-45b3-bf9e-bb788d5f3305",
   "metadata": {},
   "outputs": [],
   "source": [
    "# # len(df_i_w)\n",
    "# len(df_i)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "id": "a157413e-2b02-4f78-b41d-e276d9877dc5",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "-0.15933332800000002"
      ]
     },
     "execution_count": 9,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# 计算\n",
    "chg=np.sum(np.array(df_i_w['pct_chg'].tolist())/100*np.array(df_i_w['权重(%)weight'].tolist())/100)*100\n",
    "chg"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "id": "a6def9e1-ce81-4cc5-bd34-cb4642cafbb5",
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "H:\\anaconda3\\Lib\\site-packages\\openpyxl\\styles\\stylesheet.py:226: UserWarning: Workbook contains no default style, apply openpyxl's default\n",
      "  warn(\"Workbook contains no default style, apply openpyxl's default\")\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>日期Date</th>\n",
       "      <th>指数代码Index Code</th>\n",
       "      <th>指数中文全称Index Chinese Name(Full)</th>\n",
       "      <th>指数中文简称Index Chinese Name</th>\n",
       "      <th>指数英文全称Index English Name(Full)</th>\n",
       "      <th>指数英文简称Index Chinese Name</th>\n",
       "      <th>开盘Open</th>\n",
       "      <th>最高High</th>\n",
       "      <th>最低Low</th>\n",
       "      <th>收盘Close</th>\n",
       "      <th>涨跌Change</th>\n",
       "      <th>涨跌幅(%)Change(%)</th>\n",
       "      <th>成交量（万手）Volume(M Shares)</th>\n",
       "      <th>成交金额（亿元）Turnover</th>\n",
       "      <th>样本数量ConsNumber</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>20211210</td>\n",
       "      <td>300</td>\n",
       "      <td>沪深300指数</td>\n",
       "      <td>沪深300</td>\n",
       "      <td>CSI 300 Index</td>\n",
       "      <td>CSI 300</td>\n",
       "      <td>5046.27</td>\n",
       "      <td>5060.06</td>\n",
       "      <td>5035.81</td>\n",
       "      <td>5055.12</td>\n",
       "      <td>-23.57</td>\n",
       "      <td>-0.46</td>\n",
       "      <td>19369.52</td>\n",
       "      <td>3569.18</td>\n",
       "      <td>300</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>20211213</td>\n",
       "      <td>300</td>\n",
       "      <td>沪深300指数</td>\n",
       "      <td>沪深300</td>\n",
       "      <td>CSI 300 Index</td>\n",
       "      <td>CSI 300</td>\n",
       "      <td>5090.02</td>\n",
       "      <td>5143.84</td>\n",
       "      <td>5079.73</td>\n",
       "      <td>5083.80</td>\n",
       "      <td>28.68</td>\n",
       "      <td>0.57</td>\n",
       "      <td>21180.16</td>\n",
       "      <td>4263.77</td>\n",
       "      <td>300</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>20211214</td>\n",
       "      <td>300</td>\n",
       "      <td>沪深300指数</td>\n",
       "      <td>沪深300</td>\n",
       "      <td>CSI 300 Index</td>\n",
       "      <td>CSI 300</td>\n",
       "      <td>5066.35</td>\n",
       "      <td>5075.21</td>\n",
       "      <td>5038.86</td>\n",
       "      <td>5049.70</td>\n",
       "      <td>-34.11</td>\n",
       "      <td>-0.67</td>\n",
       "      <td>15910.78</td>\n",
       "      <td>3186.86</td>\n",
       "      <td>300</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>20211215</td>\n",
       "      <td>300</td>\n",
       "      <td>沪深300指数</td>\n",
       "      <td>沪深300</td>\n",
       "      <td>CSI 300 Index</td>\n",
       "      <td>CSI 300</td>\n",
       "      <td>5036.28</td>\n",
       "      <td>5060.37</td>\n",
       "      <td>5003.78</td>\n",
       "      <td>5005.90</td>\n",
       "      <td>-43.80</td>\n",
       "      <td>-0.87</td>\n",
       "      <td>15052.98</td>\n",
       "      <td>2910.73</td>\n",
       "      <td>300</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>20211216</td>\n",
       "      <td>300</td>\n",
       "      <td>沪深300指数</td>\n",
       "      <td>沪深300</td>\n",
       "      <td>CSI 300 Index</td>\n",
       "      <td>CSI 300</td>\n",
       "      <td>5006.61</td>\n",
       "      <td>5034.73</td>\n",
       "      <td>4987.22</td>\n",
       "      <td>5034.73</td>\n",
       "      <td>28.83</td>\n",
       "      <td>0.58</td>\n",
       "      <td>14405.39</td>\n",
       "      <td>2914.75</td>\n",
       "      <td>300</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "     日期Date  指数代码Index Code 指数中文全称Index Chinese Name(Full)  \\\n",
       "0  20211210             300                        沪深300指数   \n",
       "1  20211213             300                        沪深300指数   \n",
       "2  20211214             300                        沪深300指数   \n",
       "3  20211215             300                        沪深300指数   \n",
       "4  20211216             300                        沪深300指数   \n",
       "\n",
       "  指数中文简称Index Chinese Name 指数英文全称Index English Name(Full)  \\\n",
       "0                    沪深300                  CSI 300 Index   \n",
       "1                    沪深300                  CSI 300 Index   \n",
       "2                    沪深300                  CSI 300 Index   \n",
       "3                    沪深300                  CSI 300 Index   \n",
       "4                    沪深300                  CSI 300 Index   \n",
       "\n",
       "  指数英文简称Index Chinese Name   开盘Open   最高High    最低Low  收盘Close  涨跌Change  \\\n",
       "0                  CSI 300  5046.27  5060.06  5035.81  5055.12    -23.57   \n",
       "1                  CSI 300  5090.02  5143.84  5079.73  5083.80     28.68   \n",
       "2                  CSI 300  5066.35  5075.21  5038.86  5049.70    -34.11   \n",
       "3                  CSI 300  5036.28  5060.37  5003.78  5005.90    -43.80   \n",
       "4                  CSI 300  5006.61  5034.73  4987.22  5034.73     28.83   \n",
       "\n",
       "   涨跌幅(%)Change(%)  成交量（万手）Volume(M Shares)  成交金额（亿元）Turnover  样本数量ConsNumber  \n",
       "0            -0.46                 19369.52           3569.18             300  \n",
       "1             0.57                 21180.16           4263.77             300  \n",
       "2            -0.67                 15910.78           3186.86             300  \n",
       "3            -0.87                 15052.98           2910.73             300  \n",
       "4             0.58                 14405.39           2914.75             300  "
      ]
     },
     "execution_count": 10,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df300_daily=pd.read_excel('沪深300指数近3年.xlsx')\n",
    "df300_daily.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "id": "3ed8199c-ab4a-4178-872e-9874f346abbc",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "727   -0.17\n",
       "Name: 涨跌幅(%)Change(%), dtype: float64"
      ]
     },
     "execution_count": 11,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df300_daily[df300_daily['日期Date']==20241211]['涨跌幅(%)Change(%)']"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "f9ca502f-ac69-43e1-b6bc-a3b9fe773aff",
   "metadata": {},
   "source": [
    "## 找到指数权重前20支股票的近3年每天的涨跌幅时序数据"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "id": "6966b109-b6f5-4d94-9ed8-e5074ec002e9",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>ts_code</th>\n",
       "      <th>trade_date</th>\n",
       "      <th>open</th>\n",
       "      <th>high</th>\n",
       "      <th>low</th>\n",
       "      <th>close</th>\n",
       "      <th>pre_close</th>\n",
       "      <th>change</th>\n",
       "      <th>pct_chg</th>\n",
       "      <th>vol</th>\n",
       "      <th>amount</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>600519.SH</td>\n",
       "      <td>20241211</td>\n",
       "      <td>1540.00</td>\n",
       "      <td>1555.00</td>\n",
       "      <td>1530.98</td>\n",
       "      <td>1535.60</td>\n",
       "      <td>1546.59</td>\n",
       "      <td>-10.99</td>\n",
       "      <td>-0.7106</td>\n",
       "      <td>29671.12</td>\n",
       "      <td>4569662.604</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>600519.SH</td>\n",
       "      <td>20241210</td>\n",
       "      <td>1570.00</td>\n",
       "      <td>1579.73</td>\n",
       "      <td>1545.18</td>\n",
       "      <td>1546.59</td>\n",
       "      <td>1518.80</td>\n",
       "      <td>27.79</td>\n",
       "      <td>1.8297</td>\n",
       "      <td>60312.10</td>\n",
       "      <td>9421340.719</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>600519.SH</td>\n",
       "      <td>20241209</td>\n",
       "      <td>1522.02</td>\n",
       "      <td>1529.72</td>\n",
       "      <td>1513.20</td>\n",
       "      <td>1518.80</td>\n",
       "      <td>1521.01</td>\n",
       "      <td>-2.21</td>\n",
       "      <td>-0.1453</td>\n",
       "      <td>19799.86</td>\n",
       "      <td>3008173.586</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>600519.SH</td>\n",
       "      <td>20241206</td>\n",
       "      <td>1513.00</td>\n",
       "      <td>1538.90</td>\n",
       "      <td>1508.07</td>\n",
       "      <td>1521.01</td>\n",
       "      <td>1511.00</td>\n",
       "      <td>10.01</td>\n",
       "      <td>0.6625</td>\n",
       "      <td>32039.69</td>\n",
       "      <td>4883148.600</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>600519.SH</td>\n",
       "      <td>20241205</td>\n",
       "      <td>1510.86</td>\n",
       "      <td>1517.86</td>\n",
       "      <td>1510.00</td>\n",
       "      <td>1511.00</td>\n",
       "      <td>1520.00</td>\n",
       "      <td>-9.00</td>\n",
       "      <td>-0.5921</td>\n",
       "      <td>16141.47</td>\n",
       "      <td>2441318.387</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "     ts_code  trade_date     open     high      low    close  pre_close  \\\n",
       "0  600519.SH    20241211  1540.00  1555.00  1530.98  1535.60    1546.59   \n",
       "1  600519.SH    20241210  1570.00  1579.73  1545.18  1546.59    1518.80   \n",
       "2  600519.SH    20241209  1522.02  1529.72  1513.20  1518.80    1521.01   \n",
       "3  600519.SH    20241206  1513.00  1538.90  1508.07  1521.01    1511.00   \n",
       "4  600519.SH    20241205  1510.86  1517.86  1510.00  1511.00    1520.00   \n",
       "\n",
       "   change  pct_chg       vol       amount  \n",
       "0  -10.99  -0.7106  29671.12  4569662.604  \n",
       "1   27.79   1.8297  60312.10  9421340.719  \n",
       "2   -2.21  -0.1453  19799.86  3008173.586  \n",
       "3   10.01   0.6625  32039.69  4883148.600  \n",
       "4   -9.00  -0.5921  16141.47  2441318.387  "
      ]
     },
     "execution_count": 12,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "ts_code_20=df20['ts_code'].tolist()\n",
    "df.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "367806a3-9809-4f4b-bfa8-2b89fa096020",
   "metadata": {
    "scrolled": true
   },
   "outputs": [],
   "source": [
    "dff=pd.DataFrame(columns=list(df.columns))\n",
    "for i in df.index:\n",
    "    if df.loc[i,'ts_code'] in ts_code_20:\n",
    "        dff=pd.concat([dff,df.loc[i,:]])\n",
    "dff.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "76125cbb-2a08-42f5-a7a5-1ab2e6ff26c3",
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3 (ipykernel)",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.12.4"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}
